--SET XACT_ABORT ON
SET NOCOUNT ON
DROP TABLE IF EXISTS testtbl
CREATE TABLE testtbl 
    (id      int  NOT NULL,
     datecol date NOT NULL,
     xmlcol  xml  NULL,
     CONSTRAINT pk_testtbl PRIMARY KEY (id))
go
CREATE OR ALTER PROCEDURE inner_sp AS
   PRINT '---inner_sp starting'
   BEGIN TRANSACTION

   INSERT testtbl(id, datecol) VALUES (11, '2001-01-01')
   -- ; THROW 57000, 'I am throwing an error', 77
   -- RAISERROR('I am raising an error', 16, 1)
   -- UPDATE testtbl SET xmlcol = (SELECT query_plan FROM sys.dm_exec_query_statistics_xml(@@spid))
   -- RAISERROR('I am raising a severe error', 20, 1) WITH LOG
   -- WAITFOR DELAY '00:01:00'
   --INSERT nosuchtbl(id, datecol)
   INSERT testtbl(id, datecol)
      VALUES (12, '2002-02-02'), 
             (13, NULL), 
             --(13, 'Bad date'),
             (14, '2004-04-04')

   INSERT testtbl(id, datecol) VALUES (15, '2005-05-05')

   COMMIT TRANSACTION
   PRINT '---inner_sp completed'
go
CREATE OR ALTER PROCEDURE outer_sp AS
   PRINT 'outer_sp starting'
   EXEC inner_sp
   PRINT 'outer_sp completed'
go
EXEC outer_sp
go
SELECT id, datecol FROM testtbl ORDER BY id
IF @@trancount > 0 
BEGIN
   PRINT 'AFTER THE TEST THERE IS AN ACTIVE TRANSACTION!'
   ROLLBACK TRANSACTION
END
ELSE 
   PRINT 'No transaction is active after the test'
